import pymysql

db = pymysql.connect(host="192.168.0.26", user="root", password="root", database="test", port=3306)
cursor = db.cursor()
sql = "INSERT INTO `test`.`college`(`id`, `name`, `city`, `type`) VALUES (%s, %s, %s, %s);"
select_sql = "SELECT MAX(id) FROM college;"

with open("college.txt", encoding="utf-8") as f:
    line = f.readlines()
    names = []
    cursor.execute(select_sql)
    index = cursor.fetchone()[0]
    gba = ["广州市", "深圳市", "佛山市", "江门市", "东莞市", "中山市", "惠州市", "肇庆市", "珠海市", "香港", "澳门"]
    for l in line:
        name_city = l.split(",")
        type = None
        if gba.__contains__(name_city[1].replace("\n", "")):
            type = '1'
        index += 1
        tmp = (index, name_city[0].replace("\n", ""), name_city[1].replace("\n", ""), type)
        names.append(tmp)
    cursor.executemany(sql, names)
    db.commit()
